import pandas as pd
import pymysql


# 从数据库中读取数据
def read_data_from_db(table) -> pd.DataFrame:
    connection_params = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'wheat_visualize',
    }

    # 连接到数据库
    conn = pymysql.connect(**connection_params)
    query = f"SELECT * FROM {table}"
    # 使用 pandas 的 read_sql 函数执行查询并将结果转换为 DataFrame
    df = pd.read_sql(query, conn)
    # 关闭数据库连接
    conn.close()
    return df


# 读取output_data表中的数据为DataFrame
def read_output_data_from_db() -> pd.DataFrame:
    connection_params = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'wheat_visualize',
    }

    # 连接到数据库
    conn = pymysql.connect(**connection_params)
    query = "SELECT * FROM output_data"
    # 使用 pandas 的 read_sql 函数执行查询并将结果转换为 DataFrame
    df = pd.read_sql(query, conn)
    # 关闭数据库连接
    conn.close()
    return df


# 读取machine_data表中的数据为DataFrame
def read_machine_data_from_db() -> pd.DataFrame:
    connection_params = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'wheat_visualize',
    }
    # 连接到数据库
    conn = pymysql.connect(**connection_params)
    query = "SELECT * FROM machine_data"
    # 使用 pandas 的 read_sql 函数执行查询并将结果转换为 DataFrame
    df = pd.read_sql(query, conn)
    # 关闭数据库连接
    conn.close()
    return df


# 读取rain_data表中的数据为DataFrame
def read_rain_data_from_db() -> pd.DataFrame:
    connection_params = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'wheat_visualize',
    }
    # 连接到数据库
    conn = pymysql.connect(**connection_params)
    query = "SELECT * FROM rain_data"
    df = pd.read_sql(query, conn)
    conn.close()
    return df


# 读取seed_area_data表中的数据为DataFrame
def read_seed_area_data_from_db() -> pd.DataFrame:
    connection_params = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'wheat_visualize',
    }
    # 连接到数据库
    conn = pymysql.connect(**connection_params)
    query = "SELECT * FROM seed_area_data"
    df = pd.read_sql(query, conn)
    conn.close()
    return df


# 读取temperature_data表中的数据为DataFrame
def read_temperature_data_from_db() -> pd.DataFrame:
    connection_params = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'wheat_visualize',
    }
    # 连接到数据库
    conn = pymysql.connect(**connection_params)
    query = "SELECT * FROM temperature_data"
    df = pd.read_sql(query, conn)
    conn.close()
    return df


# 登录验证
def verify_user_login(username, password) -> bool:
    # 连接到数据库
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='wheat_visualize',
        charset='utf8mb4'
    )

    # 创建游标对象
    cursor = conn.cursor()

    try:
        # 执行查询用户的 SQL 语句
        query = "SELECT * FROM users WHERE username = %s AND password = %s"
        cursor.execute(query, (username, password))

        # 检查结果集中是否有匹配的记录
        if cursor.fetchone():
            print("用户登录成功")
            return True
        else:
            return False
    except pymysql.Error as e:
        print(f"发生错误: {str(e)}")

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()


# 用户注册
def register_user(username, password) -> bool:
    # 连接到数据库
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='wheat_visualize',
        charset='utf8mb4'
    )

    # 创建游标对象
    cursor = conn.cursor()

    try:
        # 检查用户名是否已存在
        query = "SELECT * FROM users WHERE username = %s"
        cursor.execute(query, (username,))

        if cursor.fetchone():
            print("该用户名已被使用")
            return False
        else:
            # 插入新用户到数据库
            query = "INSERT INTO users (username, password) VALUES (%s, %s)"
            cursor.execute(query, (username, password))
            conn.commit()
            print("用户注册成功")
            return True
    except pymysql.Error as e:
        conn.rollback()
        print(f"发生错误: {str(e)}")

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()


# 读取用户信息
def read_user_info():
    connection_params = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'wheat_visualize',
    }

    # 连接到数据库
    conn = pymysql.connect(**connection_params)
    query = "SELECT * FROM users"
    # 使用 pandas 的 read_sql 函数执行查询并将结果转换为 DataFrame
    df = pd.read_sql(query, conn)
    # 关闭数据库连接
    conn.close()
    return df


# 根据用户名删除用户
def del_user_data(username):
    connection_params = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'wheat_visualize',
    }

    # 连接到数据库
    conn = pymysql.connect(**connection_params)

    # 构造删除 SQL 查询语句
    query = f"DELETE FROM users WHERE username = {username}"

    # 执行删除操作
    with conn.cursor() as cursor:
        cursor.execute(query)
        conn.commit()

    # 关闭数据库连接
    conn.close()
    return True


# 根据用户名修改用户信息
def modify_user_data(username, password, status):
    connection_params = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'wheat_visualize',
    }

    # 连接到数据库
    conn = pymysql.connect(**connection_params)

    # 构造更新 SQL 查询语句
    query = f"UPDATE users SET password={password}, type = {status} WHERE username = '{username}'"

    # 执行更新操作
    with conn.cursor() as cursor:
        cursor.execute(query)
        conn.commit()

    # 关闭数据库连接
    conn.close()
    return True


# 添加用户
def add_user_data(username, password, type_):
    # 连接到数据库
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='wheat_visualize',
        charset='utf8mb4'
    )

    # 创建游标对象
    cursor = conn.cursor()

    try:
        # 检查用户名是否已存在
        query = "SELECT * FROM users WHERE username = %s"
        cursor.execute(query, (username,))

        if cursor.fetchone():
            print("该用户名已被使用")
            return False
        else:
            # 插入新用户到数据库
            query = "INSERT INTO users (username, password,type) VALUES (%s, %s, %s)"
            cursor.execute(query, (username, password, type_))
            conn.commit()
            print("用户注册成功")
            return True
    except pymysql.Error as e:
        conn.rollback()
        print(f"发生错误: {str(e)}")


# 根据data_type获取数据
def get_data_by_type(data_type):
    if data_type == 'output_data':
        return read_output_data_from_db().head(500)
    elif data_type == "machine_data":
        return read_machine_data_from_db().head(500)
    elif data_type == "seed_area_data":
        return read_seed_area_data_from_db().head(500)
    elif data_type == "rain_data":
        return read_rain_data_from_db().head(500)
    elif data_type == "temperature_data":
        return read_temperature_data_from_db().head(500)
    else:
        return None
